/*
 * Created on Oct 4, 2003
 * Local Projects Database
 * (c) 2003 eRomania Gateway Association
 *
 */
package ro.gateway.aida.stats;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Comparator;
import java.util.Iterator;
import java.util.Vector;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.db.AIDASectorPersMan;
import ro.gateway.aida.servlet.Constants;

/**
 * @author Mihai Postelnicu DataMiningDB
 *  
 */
public class StatsDB extends DBPersistenceManager {
	//TODO: throws la TOATE!
	private StatsDB(PersistenceToken token) {
		super(token);
	}

	public static StatsDB getManager(PersistenceToken token) {
		return new StatsDB(token);
	}

	public StatsHolder getSortedSectorStats(String lang, int parent_id,
			boolean show) {
		StatsHolder sh = getSectorStats(lang, parent_id, show);
		Iterator i = sh.iterator();

		while (i.hasNext()) {
			StatsItem element = (StatsItem) i.next();

			if (element.getCount() == 0)
				i.remove();
		}

		return sortStats(sh);
	}

	public int createSectorProjectsCount(Vector sects, int[] s) {
		
		
		int count = s[2];
		//if(s[0]==264 || s[1]==264) System.out.println("s1["+s[0]+"]["+s[1]+"]["+s[2]+"]="+count);
		Iterator i = sects.iterator();
		while (i.hasNext()) {
			int[] cs = (int[]) i.next();
			if (cs[0] == s[1]) {
				count += createSectorProjectsCount(sects, cs);				
			}
		}
		//if(s[0]==264 || s[1]==264) System.out.println("s2["+s[0]+"]["+s[1]+"]["+s[2]+"]="+count);
		return count;
	}

	public StatsHolder getSectorStats(String lang, int parent_id, boolean show) {
		AIDASectorPersMan asdb = AIDASectorPersMan.getManager(token);
		StatsHolder sh = asdb.getAllByLangAndParent(lang, parent_id);
		Connection con = getConnection();
		String SHOW = " ";
		if (!show)
			SHOW = SHOW_APPROVED;

		try {
			PreparedStatement ps = con.prepareStatement(GET_ASSIGNED_SECTORS1
					+ SHOW + GET_ASSIGNED_SECTORS2);
			//ps.setString(1,lang);
			ResultSet rs = ps.executeQuery();
			Vector sects = new Vector();
			while (rs.next()) {
				int[] s = new int[3];
				s[0] = rs.getInt(1);
				s[1] = rs.getInt(2);
				s[2] = rs.getInt(3);
				sects.add(s);
			}
			//for each sector, grab the subsectors count
			Iterator i = sects.iterator();
			while (i.hasNext()) {
				int[] s = (int[]) i.next();
				sh.incById(s[1],createSectorProjectsCount(sects,s));

			}

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}

		return sh;
	}

	public StatsHolder getStatusStats(String lang, boolean show) {
		StatsHolder sh = new StatsHolder("Status");
		Connection con = getConnection();
		String SHOW = " ";
		if (!show)
			SHOW = SHOW_APPROVED;

		try {
			PreparedStatement ps = con.prepareStatement(GET_STATUS_STATS1
					+ SHOW + GET_STATUS_STATS2);
			ResultSet rs = ps.executeQuery();
			long sid = -1;
			StatsItem si = null;
			while (rs.next()) {
				if (sid != rs.getLong(1)) {
					si = new StatsItem();
					sh.add(si);
					si.setId(rs.getLong(1));
					si.setCount(rs.getLong(3));
					si.getName().setViewLang(lang);
					sid = rs.getLong(1);
				}
				si.getName().setString(rs.getString(4), rs.getString(2));
			}

			rs.close();
			ps.close();

			ps = con.prepareStatement(GET_UNSPECIFIED_STATUS + SHOW);
			rs = ps.executeQuery();

			while (rs.next()) {
				si = new StatsItem();
				si.setId(0);
				si.getName().setString(Constants.DEFAULT_LANGUAGE,
						"Unspecified Status");
				si.getName().setViewLang(lang);
				si.setCount(rs.getLong(1));
				sh.add(si);
			}

			rs.close();
			ps.close();
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}

		return sortStats(sh);
	}

	public StatsHolder getCountryStats(boolean show) {
		StatsHolder sh = new StatsHolder("Countries");
		Connection con = getConnection();
		String SHOW = " ";
		if (!show)
			SHOW = SHOW_APPROVED;

		try {
			PreparedStatement ps = con.prepareStatement(GET_COUNTRY_STATS1
					+ SHOW + GET_COUNTRY_STATS2);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				StatsItem si = new StatsItem();
				si.getName().setString(Constants.DEFAULT_LANGUAGE,
						rs.getString(1));
				si.setStrId(rs.getString(2));
				si.setCount(rs.getLong(3));
				sh.add(si);
			}

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}

		return sortStats(sh);
	}

	public StatsHolder getRegionStats(boolean show) {
		StatsHolder sh = new StatsHolder("Regions");
		Connection con = getConnection();
		String SHOW = " ";
		if (!show)
			SHOW = SHOW_APPROVED;

		try {
			PreparedStatement ps = con.prepareStatement(GET_REGION_STATS1
					+ SHOW + GET_REGION_STATS2);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				StatsItem si = new StatsItem();
				si.getName().setString(Constants.DEFAULT_LANGUAGE,
						rs.getString(1));
				si.setStrId(rs.getString(2));
				si.setCount(rs.getLong(3));
				sh.add(si);
			}

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}

		return sortStats(sh);
	}

	
	public StatsHolder getFundingOrgStats(String lang, boolean show) {
		StatsHolder sh = new StatsHolder("Funding Organizations");
		Connection con = getConnection();
		String SHOW = " ";
		if (!show)
			SHOW = SHOW_APPROVED;

		try {
			PreparedStatement ps = con.prepareStatement(GET_FUNDING_ORG_STATS1
					+ SHOW + GET_FUNDING_ORG_STATS2);
			ResultSet rs = ps.executeQuery();
			StatsItem si = null;
			long cid = -1;
			while (rs.next()) {
				if (cid != rs.getLong(2)) {
					si = new StatsItem();
					si.getName().setViewLang(lang);
					si.setId(rs.getLong(2));
					si.setCount(rs.getLong(3));
					cid = rs.getLong(2);
					sh.add(si);
				}
				si.getName().setString(rs.getString(4), rs.getString(1));
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}

		return sortStats(sh);
	}

	
	
	
	public StatsHolder getSourceOrgStats(String lang, boolean show) {
		StatsHolder sh = new StatsHolder("Information Source");
		Connection con = getConnection();
		String SHOW = " ";
		if (!show)
			SHOW = SHOW_APPROVED;

		try {
			PreparedStatement ps = con.prepareStatement(GET_ORG_SOURCE_STATS1
					+ SHOW + GET_ORG_SOURCE_STATS2);
			ResultSet rs = ps.executeQuery();
			StatsItem si = null;
			long cid = -1;
			while (rs.next()) {
				if (cid != rs.getLong(2)) {
					si = new StatsItem();
					si.getName().setViewLang(lang);
					si.setId(rs.getLong(2));
					si.setCount(rs.getLong(3));
					cid = rs.getLong(2);
					sh.add(si);
				}
				si.getName().setString(rs.getString(4), rs.getString(1));
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}

		return sortStats(sh);
	}

	public static StatsHolder sortStats(StatsHolder s) {
		Comparator c = new Comparator() {
			public int compare(Object o1, Object o2) {
				StatsItem s1 = (StatsItem) o1;
				StatsItem s2 = (StatsItem) o2;

				return (int) (s2.getCount() - s1.getCount());
			}
		};
		Collections.sort(s, c);
		return s;
	}

	public static final String SHOW_APPROVED = " AND a.approved='Y' ";
	
	public static final String GET_ASSIGNED_SECTORS1="SELECT DISTINCT s1.parent_ro_aida_code,s1.ro_aida_code,cs2.cnt FROM " +
			"ro_aida_sectors s1 " +
			" LEFT OUTER JOIN " +
			" (SELECT cs.ro_aida_code, count(*) as cnt FROM " +
			" (SELECT s.ro_aida_code FROM" +
			" activities a,sectors s, objects o WHERE " +
			"s.id=o.id ";
	
	public static final String GET_ASSIGNED_SECTORS2 = "  AND o.activity_id=a.id GROUP BY " +
	"  s.ro_aida_code,a.id) cs GROUP BY cs.ro_aida_code) cs2 ON (s1.ro_aida_code = cs2.ro_aida_code)";

	//public static final String GET_ASSIGNED_SECTORS2 = " GROUP BY
	// ro_aida_code";

	public static final String GET_ORG_SOURCE_STATS1 = "SELECT od.name, od.id, count(*),od.lang FROM orgs_inv oi, orgs_desc od, "
			+ "objects o, activities a WHERE"
			+ " od.id=oi.org_id AND oi.id=o.id AND o.activity_id=a.id AND oi.role=103";

	public static final String GET_FUNDING_ORG_STATS1 = "SELECT od.name, od.id, count(*),od.lang FROM orgs_inv oi, orgs_desc od, "
		+ "objects o, activities a WHERE"
		+ " od.id=oi.org_id AND oi.id=o.id AND o.activity_id=a.id AND oi.role=1 ";

	
	public static final String GET_ORG_SOURCE_STATS2 = " GROUP BY od.id, od.lang, od.name ORDER BY od.id, od.lang,od.name";
	
	public static final String GET_FUNDING_ORG_STATS2 = " GROUP BY od.id, od.lang, od.name ORDER BY od.id, od.lang,od.name";
	
	public static final String GET_COUNTRY_STATS1 = "SELECT c.name, cs.country_iso3, count(*) FROM "
			+ "(SELECT  a.id,l.country_iso3 FROM activities a, locations l, objects o WHERE l.country_iso3 IS NOT NULL ";

	public static final String GET_COUNTRY_STATS2 = " AND l.id=o.id AND o.activity_id=a.id GROUP BY l.country_iso3,a.id) "
			+ " cs, countries_iso3 c WHERE c.iso3=cs.country_iso3 GROUP BY cs.country_iso3, c.name";

	public static final String GET_REGION_STATS1 = "SELECT c.name || ' -> ' || r.name, cs.reg_id, count(*) FROM "
			+ "(SELECT a.id,l.reg_id,l.country_iso3 FROM activities a, locations l, objects o WHERE l.country_iso3 IS NOT NULL AND l.reg_id IS NOT NULL";

	public static final String GET_REGION_STATS2 = " AND l.id=o.id AND o.activity_id=a.id GROUP BY a.id,l.country_iso3 ,l.reg_id) "
		+ " cs, countries_iso3 c, regions r WHERE r.id=cs.reg_id AND c.iso3=cs.country_iso3 GROUP BY cs.reg_id, c.name || ' -> ' || r.name";

	
	public static final String GET_STATUS_STATS1 = "SELECT a.harm_status, s.status,count(*), s.lang FROM activities a, act_status s "
			+ "WHERE s.st_code=a.harm_status ";

	public static final String GET_STATUS_STATS2 = " GROUP BY harm_status,s.status,s.lang ORDER BY a.harm_status,s.lang,s.status";

	public static final String GET_UNSPECIFIED_STATUS = "SELECT count(*) FROM activities a "
			+ "WHERE a.harm_status IS null ";
}